package cn.waka.sys.admin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cn.waka.sys.admin.object.Dic;

@Component
public class DicDao extends BaseDao {
    //根据父ID查询
    public List<Map<String, Object>> getDicList(int pid) {
        String sql = "SELECT id,`value`,icon FROM w_dic WHERE status=1 and pid=? ORDER BY sort DESC";

        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                row.put("name", rs.getString(2));
                row.put("img", rs.getString(3));
                return row;
            }
        }, pid);

        return list;
    }

    //根据父ID查询
    public List<Map<String, Object>> getTypeList(int pid) {
        String sql = "SELECT id,`value`,pid,icon,`status`,`key` FROM w_dic WHERE pid=? ORDER BY sort DESC";

        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                row.put("value", rs.getString(2));
                row.put("pid", rs.getInt(3));
                row.put("icon", rs.getString(4));
                row.put("status", rs.getInt(5));
                row.put("key", rs.getString(6));
                return row;
            }
        }, pid);

        return list;
    }

    //根据KEY模糊查询
    public List<Map<String, Object>> getDicByKey(String key) {
        String k = key + ",%";
        String sql = "SELECT id,`value`,icon,sort FROM w_dic w WHERE w.key LIKE ?";

        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                row.put("name", rs.getString(2));
                row.put("img", rs.getString(3));
                row.put("sort", rs.getString(4));
                return row;
            }
        }, k);
        return list;
    }

    //根据本ID查询
    public Dic getDicById(final int id) {
        String sql = "SELECT id,`value`,pid,icon,sort,`key` FROM w_dic w WHERE id = ?";

        List<Dic> list = this.query(sql, new RowMapper<Dic>() {

            @Override
            public Dic mapRow(ResultSet rs, int rowNum) throws SQLException {

                Dic dic = new Dic();
                dic.setId(id);
                dic.setName(rs.getString(2));
                dic.setPid(rs.getInt(3));
                dic.setIcon(rs.getString(4));
                dic.setSort(rs.getInt(5));
                dic.setKey(rs.getString(6));
                return dic;

            }

        }, id);

        return list.size() > 0 ? list.get(0) : null;
    }

    //禁用/启用
    public int isDisable(int id, int status) {
        String usql = "update w_dic set status=? where id=?";
        int rs = this.update(usql, status, id);
        return rs;
    }

    //删除一个
    public int deleteDic(int id) {
        String sql = "DELETE FROM `w_dic` WHERE id =?";
        int rs = this.update(sql, id);
        return rs;
    }

    //根据KEY模糊查询
    public List<Dic> getDicByPid(final int pid) {
        String sql = "SELECT id,`value`,pid,icon,`status`,`key` FROM w_dic WHERE FIND_IN_SET(?,`key`) AND id!=? ORDER BY pid,sort DESC";
        final Map<Integer, Dic> msAll = new LinkedHashMap<Integer, Dic>();
        this.query(sql, new RowMapper<Dic>() {
            @Override
            public Dic mapRow(ResultSet rs, int rowNum) throws SQLException {

                Dic dic = new Dic();
                dic.setId(rs.getInt(1));
                dic.setName(rs.getString(2));
                dic.setPid(rs.getInt(3));
                dic.setIcon(rs.getString(4));
                dic.setStatus(rs.getInt(5));
                dic.setKey(rs.getString(6));
                msAll.put(rs.getInt(1), dic);
                return dic;

            }

        }, new Object[]{pid, pid});

        List<Dic> ms = new ArrayList<Dic>();
        for (Dic m : msAll.values()) {
            if (m.getPid() <= pid) {
                ms.add(m);
            } else {
                msAll.get(m.getPid()).add(m);
            }
        }

        int n = ms.get(0).getKey().split(",").length;
        ms = arrTree(ms, n);

        return ms;
    }

    //根据KEY模糊查询
    public List<Dic> getDicMenu(final int pid, final int id) {
        String sql = "SELECT id,`value`,pid,icon,`status`,`key` FROM w_dic WHERE FIND_IN_SET(?,`key`) AND !FIND_IN_SET(?, `key`) ORDER BY pid,sort DESC";
        final Map<Integer, Dic> msAll = new LinkedHashMap<Integer, Dic>();
        this.query(sql, new RowMapper<Dic>() {
            @Override
            public Dic mapRow(ResultSet rs, int rowNum) throws SQLException {

                Dic dic = new Dic();
                dic.setId(rs.getInt(1));
                dic.setName(rs.getString(2));
                dic.setPid(rs.getInt(3));
                dic.setIcon(rs.getString(4));
                dic.setStatus(rs.getInt(5));
                dic.setKey(rs.getString(6));
                msAll.put(rs.getInt(1), dic);
                return dic;

            }

        }, new Object[]{pid, id});

        List<Dic> ms = new ArrayList<Dic>();
        for (Dic m : msAll.values()) {
            if (m.getPid() <= pid) {
                ms.add(m);
            } else {
                msAll.get(m.getPid()).add(m);
            }
        }

        int n = ms.get(0).getKey().split(",").length;
        ms = arrTree(ms, n);

        return ms;
    }

    //树形结构数据转换成list
    public List<Dic> arrTree(List<Dic> list, int n) {
        List<Dic> arrlist = new ArrayList<Dic>();
        for (Dic d : list) {
            int k = d.getKey().split(",").length;
            String spl = StringUtils.repeat("&nbsp;&nbsp;&nbsp;├&nbsp;&nbsp;", k - n);
            d.setSpl(spl);
            arrlist.add(d);
            if (d.getChildren() != null) {
                List<Dic> _arrlist = arrTree(d.getChildren(), n);
                arrlist.addAll(_arrlist);
            }
        }

        return arrlist;
    }

    public int insert(final Dic r) {
        KeyHolder holder = new GeneratedKeyHolder();
        this.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                String sql = "insert into w_dic(pid,`value`,icon)values(?,?,?)";
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setInt(1, r.getPid());
                ps.setString(2, r.getName());
                ps.setString(3, r.getIcon());
                return ps;
            }
        }, holder);
        int id = holder.getKey().intValue();
        return id;
    }

    public int updateDIc(Dic r) {
        int rs = this.update("update w_dic set `pid`=?,`value`=?,icon=?,`key`=? where id=?",
                r.getPid(),
                r.getName(),
                r.getIcon(),
                r.getKey(),
                r.getId());
        return rs;
    }

    public int updateKey(int id, String key) {
        int rs = this.update("update w_dic set `key`=? where id=?", key, id);
        return rs;
    }

    public int delDicTree(int id) {
        String sql = "DELETE FROM `w_dic` WHERE FIND_IN_SET(?,`key`)";
        int rs = this.update(sql, id);
        return rs;
    }

    //根据本ID查询
    public Map<Integer, String> getDicMap() {
        String sql = "SELECT id,`value`,pid,icon,sort FROM w_dic ";
        Map<Integer, String> dicMap = new HashMap<Integer, String>();
        this.query(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                dicMap.put(rs.getInt(1), rs.getString(2));
                return rs.getInt(1);
            }

        });
        return dicMap;
    }
}
